package com.rh.main;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DBhelper {
	
	 private static String url="jdbc:mysql://localhost:3306/test";
	 private static String name="root";
	 private static String password="123456";
	 
	public DBhelper() {
		super();
		// TODO Auto-generated constructor stub
	}

	public Connection getConn(){
		  try {
		   Class.forName("com.mysql.jdbc.Driver");
		   Connection conn=DriverManager.getConnection(url,name,password);
		   Statement stmt=conn.createStatement();
		   return conn;
		  } catch (ClassNotFoundException e) {
			  System.out.println("error1:"+e.toString());
		   e.printStackTrace();
		   return null;
		  } catch (SQLException e) {
			  System.out.println("error2:"+e.toString());
		   e.printStackTrace();
		   return null;
		  }
		
	}

	public  int insert(Message message) {
	    Connection conn = getConn();
	    int i = 0;
	    String sql = "insert into message (name,itemtype,headimg,addtime,imagurl,chattype,duration,content) values(?,?,?,?,?,?,?,?)";
	    PreparedStatement pstmt;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        pstmt.setString(1, message.getName());
	        pstmt.setInt(2, message.getItemtype());
	        pstmt.setString(3, message.getHeadimg());
	        pstmt.setString(4, message.getAddtime());
	        pstmt.setString(5, message.getImagurl());
	        pstmt.setInt(6, message.getChattype());
	        pstmt.setInt(7, message.getDuration());
	        pstmt.setString(8, message.getContent());
	        i = pstmt.executeUpdate();
	         pstmt.close();
	        conn.close();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}
	
	public  String query(){
		JSONObject jo = new JSONObject(); // 对象{}
		JSONArray jsonArray = new JSONArray();// 数组[]
		  Connection conn = getConn();
		/*  List<Message> lists=new ArrayList<>();*/
		 try {
			 Statement tmt= conn.createStatement();
			 String sql = "select * form message";
			 ResultSet set= tmt.executeQuery(sql);
			 while(set.next()){
			 	jo.put("name",set.getString("1"));
			 	jo.put("itemtype",set.getInt("2"));
			 	jo.put("headimg",set.getString("3"));
			 	jo.put("chattype",set.getInt("4"));
				 jo.put("addtime",set.getString("5"));
				 jo.put("content",set.getString("6"));
				 jo.put("duration",set.getInt("7"));
				 jo.put("imagurl",set.getString("8"));
				 jsonArray.add(jo);
				 /*Message msg=new Message();
				 msg.setName(set.getString("1")) ;
				 msg.setItemtype(set.getInt("2"));
				 msg.setHeadimg(set.getString("3"));
				 msg.setChattype(set.getInt("4"));
				 msg.setAddtime(set.getString("5"));
				 msg.setContent(set.getString("6"));
				 msg.setDuration(set.getInt("7"));
				 msg.setImagurl(set.getString("8"));
				 lists.add(msg);*/
			 }
			 return jsonArray.toString();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "";
		}
	}
	
	public int add(int a,int b){
		return a+b;
	}
	 
}
